package dao.impl;

import dao.UserDao;
import tools.JDBCTool;
import vo.Users;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {

    @Override
    public Users userLogin(Users users) {
        try{
            Connection conn = JDBCTool.getConnection();
            String sql = "select * from users where username=? and password=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, users.getUsername());
            ps.setString(2, users.getPassword());
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                Integer id = rs.getInt("id");
                String workCode = rs.getString("work_code");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String department = rs.getString("department");
                String post = rs.getString("post");
                String registerDate = rs.getString("register_date");
                String gender = rs.getString("gender");
                String telephone = rs.getString("telephone");
                String birthDate = rs.getString("birth_date");
                String email = rs.getString("email");
                String role = rs.getString("role");
                Integer state = rs.getInt("state");
                users = new Users(id,workCode,username,password,department,post,registerDate,gender,telephone,
                        birthDate,email,role,state);
                return users;
            }
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Users> queryUserList(String userName) {
        List<Users> list = new ArrayList<>();
        try{
            Connection conn = JDBCTool.getConnection();

            StringBuilder sql = new StringBuilder("select * from users");
            if(userName!=null && !userName.isEmpty()){
                sql.append(" where username like ?");
            }
            sql.append(" order by id desc limit 0,10");
            PreparedStatement ps = conn.prepareStatement(sql.toString());
            if(userName!=null && !userName.isEmpty()){
                ps.setString(1,userName);
            }
            ResultSet rs = ps.executeQuery();

            while(rs.next()){
                Integer id = rs.getInt("id");
                String workCode = rs.getString("work_code");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String department = rs.getString("department");
                String post = rs.getString("post");
                String registerDate = rs.getString("register_date");
                String gender = rs.getString("gender");
                String telephone = rs.getString("telephone");
                String birthDate = rs.getString("birth_date");
                String email = rs.getString("email");
                String role = rs.getString("role");
                Integer state = rs.getInt("state");
                Users user = new Users(id,workCode,username,password,department,post,registerDate,gender,telephone,
                        birthDate,email,role,state);
                list.add(user);
            }
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int insertUser(Users user) {
        try{
            Connection conn = JDBCTool.getConnection();
            String sql = "insert into users(work_code,username,password,department,post,register_date,gender,telephone,birth_date,email) value (?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement statement = conn.prepareStatement(sql);
            // 通过statement的setXX方法，完成参数的传递
            statement.setString(1, user.getWorkCode());
            statement.setString(2, user.getUsername());
            statement.setString(3, user.getPassword());
            statement.setString(4, user.getDepartment());
            statement.setString(5, user.getPost());
            statement.setString(6, user.getRegisterDate());
            statement.setString(7, user.getGender());
            statement.setString(8, user.getTelephone());
            statement.setString(9, user.getBirthDate());
            statement.setString(10, user.getEmail());
            // 完成sql语句的执行 添加、删除、更新，影响了多少行数
            int rowsNum = statement.executeUpdate();
            conn.close();
            return rowsNum;
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int deleteUser(int id) {
        try{
            String sql = "delete from users where id=?";
            Connection conn = JDBCTool.getConnection();
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, id);
            int rowsNum = statement.executeUpdate();
            conn.close();
            if (rowsNum>0){
                System.out.println("数据已经删除成功！！！");
            }else {
                System.out.println("数据删除异常！！！");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updateUser(Users user) {
        try{
            String sql = "update users set work_code=?,username=?,password=?,department=?,post=?,gender=?,telephone=?,email=? where id=?";
            Connection conn = JDBCTool.getConnection();
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, user.getWorkCode());
            statement.setString(2, user.getUsername());
            statement.setString(3, user.getPassword());
            statement.setString(4, user.getDepartment());
            statement.setString(5, user.getPost());
            statement.setString(6, user.getGender());
            statement.setString(7, user.getTelephone());
            statement.setString(8, user.getEmail());
            statement.setInt(9, user.getId());
            int rows = statement.executeUpdate();
            conn.close();
            return rows;
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Users queryUserById(int id) {
        try{
            Connection conn = JDBCTool.getConnection();
            String sql = "select * from users where id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            Users users = null;
            while(rs.next()){
                String workCode = rs.getString("work_code");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String department = rs.getString("department");
                String post = rs.getString("post");
                String registerDate = rs.getString("register_date");
                String gender = rs.getString("gender");
                String telephone = rs.getString("telephone");
                String birthDate = rs.getString("birth_date");
                String email = rs.getString("email");
                String role = rs.getString("role");
                Integer state = rs.getInt("state");
                users = new Users(id,workCode,username,password,department,post,registerDate,gender,telephone,
                        birthDate,email,role,state);
            }
            conn.close();
            return users;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
}
